from flask import current_app,Blueprint,request,jsonify,make_response
from xlrd import open_workbook
import os
from utils.myf import myf
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
from utils.mydb import db1
from utils.xtree import xtree
from utils.getdata import getdata
from utils.myes import Es
import json
from utils.myredis import r


bp_company = Blueprint("bp_company",__name__)

@bp_company.route("/test1")
def test():
    str = "请求ip:"+request.remote_addr+"请求url:"+request.url+"错误信息:"+'/n'
    # current_app.logger.info(str)
    # current_app.logger.warning(str)
    current_app.logger.error(str)
    return "ok"



@bp_company.route('/upload',methods=["POST"])
def upload():
    # 获取文件
    file = request.files['file']

    newname = myf.rename(file.filename)

    # 上传文件
    file.save(os.path.join('./static/files/' + newname))
    # 打开文件读取
    name = './static/files/' + newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(1, nrows):
        row_date = sh.row_values(i)
        print(row_date[0])
        print(row_date[1])
    return jsonify({"msg":"ok"})

@bp_company.route('/download',methods=["POST","GET"])
def download():
    # 实例化二进制数据
    out=BytesIO()
    #  创建一个Excel实例
    workbook=xlsxwriter.Workbook(out)
    #  添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table=workbook.add_worksheet()
    #  table.write(行, 列, 字段名)
    #  行、列从 0 开始计数
    #  字段命名根据每个人使用情况而定，插入数据部分也是
    sql="select * from company limit 0,10"
    res=db1.find_all(sql)
    table.write(0,0,"编号")
    table.write(0,1,"名称")
    num=1
    for i in res:
        table.write(num,0,i['id'])
        table.write(num,1,i['name'])
        num += 1
        workbook.close()
    filename=quote("{}.xls".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file=make_response(out.getvalue())
    out.close()
    mime_type=mimetypes.guess_type('%s.xls'%filename)[0]
    file.headers['Content-Type']=mime_type
    file.headers["Cache-Control"]="no-cache"
    file.headers['Content-Disposition']="attachment; filename*=utf-8''{}".format(filename)
    return file

@bp_company.route("/group")
def group():
    sql ="select * from company"
    res = db1.find_all(sql)
    data = xtree(res)
    return jsonify({"code":200,"list":data})

@bp_company.route("/addcompany",methods=["POST"])
def addcompany():
    data = getdata()
    sql = "insert into company(name,pid) values('%s',%d)"%(data["name"],int(data["pid"]))
    db1.update(sql)
    return jsonify({"code":200,"msg":"ok"})

@bp_company.route("/userslist")
def userslist():
    sql ="select * from users"
    res = db1.find_all(sql)

    return jsonify({"code":200,"list":res})

@bp_company.route("/deptesinsert")
def deptesinsert():
    es=Es("testes","test-type")
    #插入数据
    sql="select * from users limit 0,10"
    res=db1.find_all(sql)
    for i in res:
        es.insert(i)
    return "ok" 
@bp_company.route("/getdatas")
def getdatas():
    test = request.args.get("test")
    es=Es("testes","test-type")
    query={"match":{"name":test}}
    #插入数据
    res = es.find(query)
    lst = []
    for i in res["hits"]["hits"]:
        lst.append(i["_source"])
    print(lst)
    return jsonify({"code":200,"list":lst})
@bp_company.route("/getResource")
@bp_company.route('/worklist')
def worklist():
    sql = "select * from workflow"
    res = db1.find_all(sql)
    return jsonify({"code":200,'list':res})

#获取数据源接口
@bp_company.route('/getresourcedata')
def getresourcedata():
    #从redis取，如果有直接返回
    values = r.get_str('dataresouce')
    if values:
        list = eval(values)
    else:
        #如果没有查询
        sql = "select * from data_source"
        res = db1.find_all(sql)
        list = []
        if res:
            for i in res:
                dict={'id':i['id'],'name':i['name']}
                sql1 = "select id,name,type from %s"%(i['tablename'])
                subres = db1.find_all(sql1)
                dict['mes'] = subres
                list.append(dict)
        #存入redis
        r.set_str('dataresouce',json.dumps(list))
    return jsonify({"code":200,'list':list})
